package compareDatabase;

import java.sql.*;
import java.util.*;

public class CompareObj {
    private final static String ali_url="jdbc:oracle:thin:@47.92.149.100:1521:ytym";
    private final static String ymzx_url="jdbc:oracle:thin:@172.18.220.35:1521/ytym";
    private final static String ymzx_cs_url="jdbc:oracle:thin:@10.18.120.27:1521:ytym";
    private final static String userName = "ymzx";
    private final static String passWorld = "hzljsoft#!qazwsx";
    private final static String userName2 = "jkzx";
    private final static String passWorld2 = "ivaneve@2017!";
    private final static String DRIVER = "oracle.jdbc.driver.OracleDriver";
    static{
        try {
            Class.forName(DRIVER).newInstance();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public static void main(String[] args)throws Exception{
        CompareObj com = new CompareObj();
        Connection ali_con = com.getConnection(ali_url,userName2,passWorld2);
        Connection ym_con =
                            com.getConnection(ymzx_url,userName,passWorld);
                          //  com.getConnection(ymzx_cs_url,userName,passWorld2);
        System.out.println("已连接两个数据库");
        String sql1 = "select table_name from user_tables where table_name like 'YM%' ";
        if(ali_con!=null&&ym_con!=null){
            Statement stmt1 = ali_con.createStatement();
            Statement stmt2 = ym_con.createStatement();
            List list1 = com.Rs2List(com.getRsBySQL(sql1,stmt1));
            List list2 = com.Rs2List(com.getRsBySQL(sql1,stmt2));
            com.compare(list1,list2,stmt1,stmt2);
            if(stmt1!=null){
                stmt1.close();
                ali_con.close();
            }
            if(stmt2!=null){
                stmt2.close();
                ym_con.close();
            }
        }
    }
    //找出缺少的表
    private void compare(List list1, List list2, Statement st1, Statement st2) throws Exception {
        System.out.println("开始比较");
        /*for (Object name : list1 ) {
             String tableName = name==null?"":(String) name;
            if (list2.contains(tableName)) {
                this.TableCompare(tableName, st1, st2);
            } else {
                System.out.println("生产库中，缺少表:" + tableName);
            }
        }*/
        List<Object> diff = new ArrayList<>();
        Map<Object,Integer> map = new HashMap<>();
        for(Object name:list1){
            map.put(name,1);
        }
        for(Object name:list2){
            if(map.get(name)!=null){
                map.put(name,2);
                //找出不同的字段
                TableCompare2((String)name,st1,st2);
                continue;
            }
            diff.add("阿里云中缺少_"+name);

        }
        for(Map.Entry<Object,Integer> entry:map.entrySet()){
            if(entry.getValue()==1){
                diff.add("生产库中缺少_"+entry.getKey());
            }
        }

        for(Object obj : diff){
            System.out.println(obj);
        }
    }

   //比较表中字段的差异
    private void TableCompare2(String name, Statement st1, Statement st2) throws Exception {
        String sql = "select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from USER_TAB_COLUMNS where TABLE_NAME='" + name + "' ";
        List<TableColumns> list1 = this.parseColumnList(this.getRsBySQL(sql, st1));
        List<TableColumns> list2 = this.parseColumnList(this.getRsBySQL(sql, st2));
        Map<TableColumns,Integer> map = new HashMap<>();
        List<TableColumns> diff = new ArrayList<>();
        for(TableColumns tableColumns : list1){
            map.put(tableColumns,1);
        }
        for(TableColumns tableColumns : list2){
            if(map.get(tableColumns)!=null){
                map.put(tableColumns,2);
                continue;
            }
            diff.add(tableColumns);
        }
        for(Map.Entry<TableColumns,Integer> entry : map.entrySet()){
            if(entry.getValue()==1){
                diff.add(entry.getKey());
            }
        }
        for(TableColumns tableColumns : diff){
            System.out.println("表"+name+"中字段"+tableColumns.getCOLUMN_NAME()+"有改动");
        }
    }
    //通过结果集获得list集合
    private List<TableColumns> parseColumnList(ResultSet rs1) throws Exception {
       // Map map = new HashMap();
        List<TableColumns> list = new ArrayList<>();
        while (rs1.next()) {
            TableColumns tableColumns = new TableColumns();
            tableColumns.setCOLUMN_NAME(rs1.getString("COLUMN_NAME"));
            tableColumns.setDATA_TYPE((rs1.getString("DATA_TYPE")));
            tableColumns.setDATA_LENGTH(rs1.getString("DATA_LENGTH"));
            list.add(tableColumns);
            //map.put(rs1.getString("COLUMN_NAME"), rs1.getString("DATA_TYPE"));
        }
        return list;
    }
    //通过结果集获得list集合
    private List Rs2List(ResultSet rs1) throws Exception {
        List<String> list = new ArrayList<>();
        while (rs1.next()) {
            list.add(rs1.getString("TABLE_NAME"));
        }
        return list;
    }
    //执行sql获得结果集
    private ResultSet getRsBySQL(String sql, Statement stmt) throws Exception {
        return stmt.executeQuery(sql);
    }

    //获得连接
    private Connection getConnection(String url,String name,String pass){
        try {
            return DriverManager.getConnection(url,name,pass);
        }catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
}
